/*
 * This file is part of Shopping Lists Organizer software
 *
 * Copyright (C) 2011 Denis Kvita <dkvita@gmail.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see <http://www.gnu.org/licenses/>.
 */

#include "shoppinglistsbrowser.h"

#include <QtGui>
#include <QtSql>

#include "shoppinglistmodel.h"
#include "calendar.h"
#include "listimportdlg.h"

shoppingListsBrowser::shoppingListsBrowser(QWidget *parent)
	: QWidget(parent)
{
	setWindowTitle(tr("Shopping List Browser"));

	QSettings cfg(qApp->property("dataDir").toString()+"/settings.ini", QSettings::IniFormat);

	listD= new shoppingListModel;

	listP= new QSortFilterProxyModel;
	listP->setSourceModel(listD);
	listP->setDynamicSortFilter(true);
	listP->setFilterKeyColumn(1);
	listP->setFilterRegExp(QRegExp("*", Qt::CaseInsensitive, QRegExp::WildcardUnix));

	listV= new QTableView;
	listV->setModel(listP);
	listV->verticalHeader()->setDefaultSectionSize(22);
	listV->verticalHeader()->setMovable(true);
	listV->horizontalHeader()->setFixedHeight(22);
	listV->horizontalHeader()->setSortIndicatorShown(true);
	listV->horizontalHeader()->setMovable(true);
	listV->horizontalHeader()->setContextMenuPolicy(Qt::ActionsContextMenu);
	listV->horizontalHeader()->setTextElideMode(Qt::ElideMiddle);
	listV->setSortingEnabled(true);
	listV->setEditTriggers(QAbstractItemView::DoubleClicked|QAbstractItemView::EditKeyPressed|QAbstractItemView::AnyKeyPressed);
	listV->setSelectionBehavior(QAbstractItemView::SelectItems);
	listV->setSelectionMode(QAbstractItemView::SingleSelection);
	connect(listV->selectionModel(),SIGNAL(selectionChanged(QItemSelection,QItemSelection)),this,SLOT(onSelectionChanged(QItemSelection,QItemSelection)));

	QByteArray hDat= QByteArray::fromBase64(cfg.value("/listBrowser/header").toByteArray());
	if(!hDat.isEmpty()){
		hDat= qUncompress(hDat);
		if(!hDat.isEmpty()) listV->horizontalHeader()->restoreState(hDat);
	}

	QAction *a;
	for(int i=0; i<listD->columnCount(); i++){
		a= new QAction(listD->headerData(i,Qt::Horizontal).toString(),listV->horizontalHeader());
		a->setData(i);
		a->setCheckable(true);
		a->setChecked(listV->horizontalHeader()->isSectionHidden(i) ? Qt::Unchecked : Qt::Checked );
		connect(a, SIGNAL(triggered(bool)), this, SLOT(onHeaderSectionShow(bool)));
		listV->horizontalHeader()->addAction(a);
	}

	listDateBegin= new QDateEdit;
	listDateBegin->setCalendarPopup(true);
	listDateBegin->setCalendarWidget(new Calendar());
	if(QLocale::system().country() == QLocale::RussianFederation || QLocale::system().country() == QLocale::Ukraine) listDateBegin->calendarWidget()->setFirstDayOfWeek(Qt::Monday);
	listDateBegin->setDate(cfg.value("/listBrowser/dateBegin",QDate::currentDate()).toDate());
	connect(listDateBegin, SIGNAL(dateChanged(QDate)), this, SLOT(onDateBeginChanged(QDate)));

	QLabel *dateRangeL= new QLabel("-");

	listDateEnd= new QDateEdit;
	listDateEnd->setCalendarPopup(true);
	listDateEnd->setCalendarWidget(new Calendar());
	if(QLocale::system().country() == QLocale::RussianFederation || QLocale::system().country() == QLocale::Ukraine) listDateEnd->calendarWidget()->setFirstDayOfWeek(Qt::Monday);
	listDateEnd->setDate(cfg.value("/listBrowser/dateEnd",QDate::currentDate()).toDate());
	connect(listDateEnd, SIGNAL(dateChanged(QDate)), this, SLOT(onDateEndChanged(QDate)));

	listFilterV= new QComboBox;
	listFilterV->setEditable(true);
	listFilterV->setInsertPolicy(QComboBox::InsertAfterCurrent);
	listFilterV->setDuplicatesEnabled(false);
	listFilterV->addItem("*");
	listFilterV->completer()->setCompletionMode(QCompleter::PopupCompletion);
	listFilterV->setToolTip(tr("enter a shopping list title's filter <br><b>'*'</b>- imply any number of any characters <br><b>'?'</b>- imply one any character"));
	connect(listFilterV->lineEdit(), SIGNAL(textEdited(QString)), this, SLOT(onListFilterEdited(QString)));
	connect(listFilterV, SIGNAL(currentIndexChanged(int)), this, SLOT(onListFilterChanged(int)));

	QLabel *sumPlanL= new QLabel(tr("Total planned: "));
	sumPlanL->setAlignment(Qt::AlignRight|Qt::AlignVCenter);
	sumPlanV= new QLabel;
	QFont bFnt= font();
	bFnt.setBold(true);
	sumPlanV->setFont(bFnt);

	QLabel *sumBuyL= new QLabel(tr("Total bought: "));
	sumBuyL->setAlignment(Qt::AlignRight|Qt::AlignVCenter);
	sumBuyV= new QLabel;
	sumBuyV->setFont(bFnt);

	importList= new QToolButton;
	importList->setIcon(QIcon(":/img/list-import.png"));
	importList->setToolTip(tr("import shopping list from file"));
	importList->setAutoRaise(true);
	connect(importList, SIGNAL(released()), this, SLOT(onImportList()));

	exportList= new QToolButton;
	exportList->setIcon(QIcon(":/img/list-export.png"));
	exportList->setToolTip(tr("export selected shopping list to file"));
	exportList->setAutoRaise(true);
	exportList->setEnabled(false);
	connect(exportList, SIGNAL(released()), this, SLOT(onExportList()));

	printList= new QToolButton;
	printList->setIcon(QIcon(":/img/printer.png"));
	printList->setToolTip(tr("print selected shopping list"));
	printList->setAutoRaise(true);
	printList->setEnabled(false);
	connect(printList, SIGNAL(released()), this, SLOT(onPrintList()));

	connect(this, SIGNAL(guiControlsSwitch(bool)), exportList, SLOT(setEnabled(bool)));
	connect(this, SIGNAL(guiControlsSwitch(bool)), printList, SLOT(setEnabled(bool)));
	connect(listD, SIGNAL(totalChanged()), this, SLOT(updateTotals()));

	QHBoxLayout *lstLt= new QHBoxLayout;
	lstLt->addWidget(listDateBegin);
	lstLt->addWidget(dateRangeL);
	lstLt->addWidget(listDateEnd);
	lstLt->addWidget(listFilterV,1);
	lstLt->addWidget(importList);
	lstLt->addWidget(exportList);
	lstLt->addWidget(printList);
	lstLt->setMargin(0);
	lstLt->setSpacing(2);

	QHBoxLayout *sumLt= new QHBoxLayout;
	sumLt->addWidget(sumPlanL);
	sumLt->addWidget(sumPlanV);
	sumLt->addSpacing(20);
	sumLt->addWidget(sumBuyL);
	sumLt->addWidget(sumBuyV);
	sumLt->addStretch(1);
	sumLt->setMargin(0);
	sumLt->setSpacing(2);

	QVBoxLayout *mlt= new QVBoxLayout;
	mlt->addLayout(lstLt);
	mlt->addWidget(listV,1);
	mlt->addLayout(sumLt);
	mlt->setMargin(3);

	setLayout(mlt);
}

shoppingListsBrowser::~shoppingListsBrowser()
{

}

void shoppingListsBrowser::onDateBeginChanged(const QDate& date)
{
	updateData();
}

void shoppingListsBrowser::onDateEndChanged(const QDate& date)
{
	updateData();
}

void shoppingListsBrowser::onListFilterEdited(const QString &text)
{
	listP->setFilterRegExp(QRegExp(text,Qt::CaseInsensitive,QRegExp::Wildcard));
	updateTotals();
}

void shoppingListsBrowser::onListFilterChanged(int index)
{
	listP->setFilterRegExp(QRegExp(listFilterV->itemText(index),Qt::CaseInsensitive,QRegExp::Wildcard));
	updateTotals();
}

int shoppingListsBrowser::selectedList()
{
	return listP->mapToSource(listV->selectionModel()->selectedIndexes().at(0)).row();
}

void shoppingListsBrowser::saveSettings()
{
	QSettings cfg(qApp->property("dataDir").toString()+"/settings.ini", QSettings::IniFormat);
	cfg.setValue("/listBrowser/dateBegin",listDateBegin->date());
	cfg.setValue("/listBrowser/dateEnd",listDateEnd->date());
	cfg.setValue("/listBrowser/filter",listFilterV->currentText());
	cfg.setValue("/listBrowser/header",qCompress(listV->horizontalHeader()->saveState()).toBase64());
}

void shoppingListsBrowser::onImportList()
{
	QSettings cfg(qApp->property("dataDir").toString()+"/settings.ini", QSettings::IniFormat);

	QString ifn= QFileDialog::getOpenFileName(this,tr("Import"), cfg.value("/listBrowser/lastDir","./").toString(),tr("Shopping Lists (*.lst);;All files (*.*)"));
	if(ifn.isEmpty()) return;
	cfg.setValue("/listBrowser/lastDir",QFileInfo(ifn).absolutePath());

	QSqlDatabase dbi= QSqlDatabase::addDatabase("QSQLITE","import");
	dbi.setDatabaseName(ifn);
	if(!dbi.open()){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Can't open file!"),QMessageBox::Ok);
		return;
	}

	QStringList tables= dbi.tables();
	if(!tables.contains("purchase") || !tables.contains("shop")){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Invaid file!"),QMessageBox::Ok);
		return;
	}
	QSqlRecord pRcd= dbi.record("purchase");
	if(pRcd.count()!=12){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Purchase data has invalid format!"),QMessageBox::Ok);
		return;
	}

	QSqlRecord sRcd= dbi.record("shop");
	if(sRcd.count()!=3){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Shop data has invalid format!"),QMessageBox::Ok);
		return;
	}

	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlRecord pRcdRef= dbc.record("purchase");

	for(int ip=0; ip< pRcd.count(); ip++) if(pRcd.fieldName(ip) != pRcdRef.fieldName(ip)){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Purchase data has invalid format!"),QMessageBox::Ok);
		return;
	}

	QSqlRecord sRcdRef= dbc.record("shop");

	for(int s=0; s< sRcd.count(); s++) if(sRcd.fieldName(s) != sRcdRef.fieldName(s)){
		QMessageBox::critical(this,tr("Error"),tr("Error while import list from file '")+QFileInfo(ifn).absoluteFilePath()+tr("'! Shop data has invalid format!"),QMessageBox::Ok);
		return;
	}

	QSqlQuery qi(dbi);
	QSqlQuery qu(dbi);
	QSqlQuery q(dbc);
	QSqlQuery qw(dbc);

	// import shops
	QString insShopOID= "INSERT INTO shop(oid,sTitle,sAddress,sGeoLoc) VALUES(:I,:T,:A,:G)";
	QString insShopNew= "INSERT INTO shop(sTitle,sAddress,sGeoLoc) VALUES(:T,:A,:G)";
	q.prepare("SELECT oid,sTitle,sAddress,sGeoLoc FROM shop WHERE oid=:ID");
	qw.prepare(insShopOID);

	qi.prepare("SELECT oid,sTitle,sAddress,sGeoLoc FROM shop");
	qi.exec();
	while(qi.next()){
		q.bindValue(":ID",qi.value(0));
		if(!q.exec()) qDebug()<< q.lastError().text();
		if(q.next()){
			if(qi.value(1).toString() != q.value(1).toString()
			   || qi.value(2).toString() != q.value(2).toString()
			   || qi.value(3).toString() != q.value(3).toString()){
				qw.prepare(insShopNew);
				qw.bindValue(":T", qi.value(1));
				qw.bindValue(":A", qi.value(2));
				qw.bindValue(":G", qi.value(3));
				qw.exec();
				qw.prepare("SELECT last_insert_rowid()");
				qw.exec();
				if(qw.next()){
					QVariant oid= qw.value(0);
					qu.prepare("UPDATE purchase SET pShop=:SN WHERE pShop=:SO");
					qu.bindValue(":SN",oid);
					qu.bindValue(":SO",q.value(0));
					qu.exec();
				}
			}
		}else{
			qw.bindValue(":I", qi.value(0));
			qw.bindValue(":T", qi.value(1));
			qw.bindValue(":A", qi.value(2));
			qw.bindValue(":G", qi.value(3));
			if(qw.exec()) qDebug()<<qw.lastError().text();
		}
	}

	// import purchases
	QSqlQuery qr(dbc);
	qr.prepare("SELECT DISTINCT pDate,pList FROM purchase WHERE pDate=:D AND pList=:L");

	q.prepare("SELECT DISTINCT pDate,pList FROM purchase WHERE pDate=:D AND pList=:L");

	qi.prepare("SELECT DISTINCT pDate,pList FROM purchase");
	qi.exec();
	if(!qi.next())return;
	QString lTitle= qi.value(1).toString();
	QDate lDate= qi.value(0).toDate();
	q.bindValue(":D",lDate.toString("yyyy-MM-dd"));
	q.bindValue(":L",lTitle);
	q.exec();
	if(q.next()){
		listImportDlg *dlg= new listImportDlg(lTitle,lDate,this);
		if(dlg->exec() != QDialog::Accepted) return;
		if(dlg->answer->currentIndex() == 0){
			// rename
			qu.prepare("SELECT pDate, pList, pShop, pSec, pCat, pTitle, pPrcP, "
					   "pCntP, pPrcB, pCntB, pJoy, pDone FROM purchase");
			if(!qu.exec()) qDebug()<<"qu.exec()"<< qu.lastError().text();
			qw.prepare("INSERT INTO purchase(pDate, pList, pShop, pSec, pCat,"
					   " pTitle, pPrcP, pCntP, pPrcB, pCntB, pJoy, pDone)"
					   " VALUES(:D,:L,:S,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
			while(qu.next()){
				qw.bindValue(":D", dlg->newDate->date().toString("yyyy-MM-dd"));
				qw.bindValue(":L", dlg->newTitle->text());
				qw.bindValue(":S", qu.value(2));
				qw.bindValue(":SC", qu.value(3));
				qw.bindValue(":C", qu.value(4));
				qw.bindValue(":T", qu.value(5));
				qw.bindValue(":PP", qu.value(6));
				qw.bindValue(":CP", qu.value(7));
				qw.bindValue(":PB", qu.value(8));
				qw.bindValue(":CB", qu.value(9));
				qw.bindValue(":J", qu.value(10));
				qw.bindValue(":DN", qu.value(11));
				if(!qw.exec()) qDebug()<<"qw.exec()"<< qw.lastError().text();
			}
		}else{ // import
			qu.prepare("SELECT pDate, pList, pShop, pSec, pCat, pTitle, pPrcP, "
					   "pCntP, pPrcB, pCntB, pJoy, pDone FROM purchase");
			if(!qu.exec()) qDebug()<<"qu.exec()"<< qu.lastError().text();
			while(qu.next()){
				if(dlg->mergeDetail->currentIndex() == 2){
					qw.prepare("INSERT INTO purchase(pDate, pList, pShop, pSec, pCat,"
							   " pTitle, pPrcP, pCntP, pPrcB, pCntB, pJoy, pDone)"
							   " VALUES(:D,:L,:S,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
					qw.bindValue(":D", dlg->newDate->date().toString("yyyy-MM-dd"));
					qw.bindValue(":L", dlg->newTitle->text());
					qw.bindValue(":S", qu.value(2));
					qw.bindValue(":SC", qu.value(3));
					qw.bindValue(":C", qu.value(4));
					qw.bindValue(":T", qu.value(5));
					qw.bindValue(":PP", qu.value(6));
					qw.bindValue(":CP", qu.value(7));
					qw.bindValue(":PB", qu.value(8));
					qw.bindValue(":CB", qu.value(9));
					qw.bindValue(":J", qu.value(10));
					qw.bindValue(":DN", qu.value(11));
					if(!qw.exec()) qDebug()<<"new and old - qw.exec()"<< qw.lastError().text();
				}else{
					q.prepare("SELECT oid FROM purchase WHERE pDate=:D AND pList=:L AND pTitle=:T");
					q.bindValue(":D", lDate.toString("yyyy-MM-dd"));
					q.bindValue(":L", lTitle);
					q.bindValue(":T", qu.value(5));
					q.exec();
					if(q.next()){
						if(dlg->mergeDetail->currentIndex() == 0){
							qw.prepare("REPLACE INTO purchase(oid,pDate, pList, pShop, pSec, pCat,"
									   " pTitle, pPrcP, pCntP, pPrcB, pCntB, pJoy, pDone)"
									   " VALUES(:OID,:D,:L,:S,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
							qw.bindValue(":OID", q.value(0));
							qw.bindValue(":D", dlg->newDate->date().toString("yyyy-MM-dd"));
							qw.bindValue(":L", dlg->newTitle->text());
							qw.bindValue(":S", qu.value(2));
							qw.bindValue(":SC", qu.value(3));
							qw.bindValue(":C", qu.value(4));
							qw.bindValue(":T", qu.value(5));
							qw.bindValue(":PP", qu.value(6));
							qw.bindValue(":CP", qu.value(7));
							qw.bindValue(":PB", qu.value(8));
							qw.bindValue(":CB", qu.value(9));
							qw.bindValue(":J", qu.value(10));
							qw.bindValue(":DN", qu.value(11));
							if(!qw.exec()) qDebug()<<"replace - qw.exec()"<< qw.lastError().text();
						}
					}else{
						qw.prepare("INSERT INTO purchase(pDate, pList, pShop, pSec, pCat,"
								   " pTitle, pPrcP, pCntP, pPrcB, pCntB, pJoy, pDone)"
								   " VALUES(:D,:L,:S,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
						qw.bindValue(":D", dlg->newDate->date().toString("yyyy-MM-dd"));
						qw.bindValue(":L", dlg->newTitle->text());
						qw.bindValue(":S", qu.value(2));
						qw.bindValue(":SC", qu.value(3));
						qw.bindValue(":C", qu.value(4));
						qw.bindValue(":T", qu.value(5));
						qw.bindValue(":PP", qu.value(6));
						qw.bindValue(":CP", qu.value(7));
						qw.bindValue(":PB", qu.value(8));
						qw.bindValue(":CB", qu.value(9));
						qw.bindValue(":J", qu.value(10));
						qw.bindValue(":DN", qu.value(11));
						if(!qw.exec()) qDebug()<<"append - qw.exec()"<< qw.lastError().text();
					}
				}
			}
		}
		// import list`s visual settings
		qu.prepare("SELECT pDate,pList,headerState FROM listSettings");
		qu.exec();
		if(qu.next()){
			qw.prepare("INSERT INTO listSettings(pDate, pList, headerState) "
					   " VALUES(:DT,:L,:H)");
			qw.bindValue(":DT", dlg->newDate->date().toString("yyyy-MM-dd"));
			qw.bindValue(":L", dlg->newTitle->text());
			qw.bindValue(":H", qu.value(2));
			qw.exec();
		}
		delete dlg;
	}else{
		// no conflicts
		qu.prepare("SELECT pDate, pList, pShop, pSec, pCat, pTitle, pPrcP, "
				   "pCntP, pPrcB, pCntB, pJoy, pDone FROM purchase");
		if(!qu.exec()) qDebug()<<"qu.exec()"<< qu.lastError().text();
		qw.prepare("INSERT INTO purchase(pDate, pList, pShop, pSec, pCat,"
				   " pTitle, pPrcP, pCntP, pPrcB, pCntB, pJoy, pDone)"
				   " VALUES(:D,:L,:S,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
		while(qu.next()){
			qw.bindValue(":D", qu.value(0).toDate().toString("yyyy-MM-dd"));
			qw.bindValue(":L", qu.value(1));
			qw.bindValue(":S", qu.value(2));
			qw.bindValue(":SC", qu.value(3));
			qw.bindValue(":C", qu.value(4));
			qw.bindValue(":T", qu.value(5));
			qw.bindValue(":PP", qu.value(6));
			qw.bindValue(":CP", qu.value(7));
			qw.bindValue(":PB", qu.value(8));
			qw.bindValue(":CB", qu.value(9));
			qw.bindValue(":J", qu.value(10));
			qw.bindValue(":DN", qu.value(11));
			if(!qw.exec()) qDebug()<<"qw.exec()"<< qw.lastError().text();
		}
		// import list`s visual settings
		qu.prepare("SELECT pDate,pList,headerState FROM listSettings");
		qu.exec();
		if(qu.next()){
			qw.prepare("INSERT INTO listSettings(pDate, pList, headerState) "
					   " VALUES(:DT,:L,:H)");
			qw.bindValue(":DT", qu.value(0).toDate().toString("yyyy-MM-dd"));
			qw.bindValue(":L", qu.value(1));
			qw.bindValue(":H", qu.value(2));
			qw.exec();
		}
	}

	updateData();
}

void shoppingListsBrowser::onExportList()
{
	QSettings cfg(qApp->property("dataDir").toString()+"/settings.ini", QSettings::IniFormat);

	QSqlDatabase dbe= QSqlDatabase::addDatabase("QSQLITE","export");

	QString wDate= listP->mapFromSource(listD->index(listP->mapToSource(listP->index(selectedList(),0)).row(),0)).data().toDate().toString("yyyy-MM-dd");
	QString wList= listP->mapFromSource(listD->index(listP->mapToSource(listP->index(selectedList(),0)).row(),1)).data().toString();

	QString efn= QFileDialog::getSaveFileName(this,tr("Export"), cfg.value("/listBrowser/lastDir","./").toString()+"/"+wDate+"_"+wList+".lst",tr("Shopping Lists (*.lst);;All files (*.*)"));
	if(efn.isEmpty()) return;
	if(QFileInfo(efn).suffix()!="lst") efn+=".lst";
	cfg.setValue("/listBrowser/lastDir",QFileInfo(efn).absolutePath());

	if(QFile::exists(efn)) if(!QFile::remove(efn)) qDebug()<< "Can't remove file";

	dbe.setDatabaseName(efn);
	if(!dbe.open()){
		QMessageBox::critical(this,tr("Error"),tr("Error while export list '")+wList+tr("' to file '")+QFileInfo(efn).absoluteFilePath()+tr("'! Can't open file!"),QMessageBox::Ok);
		return;
	}

	QSqlQuery qe(dbe);
	qe.prepare("CREATE TABLE purchase ("
			   "pDate TEXT, "
			   "pList TEXT, "
			   "pShop INTEGER, "
			   "pSec TEXT, "
			   "pCat TEXT, "
			   "pTitle TEXT, "
			   "pPrcP REAL, "
			   "pCntP REAL, "
			   "pPrcB REAL, "
			   "pCntB REAL, "
			   "pJoy REAL, "
			   "pDone INTEGER)");
	if(!qe.exec()){
		qCritical()<<"shoppingListsBrowser::onExportList(): Can't create database(error during table 'purchase' creation)!";
	}

	qe.prepare("CREATE TABLE shop ("
			   "sTitle TEXT NOT NULL, "
			   "sAddress TEXT, "
			   "sGeoLoc TEXT)");
	if(!qe.exec()){
		qCritical()<<"shoppingListsBrowser::onExportList(): Can't create database(error during table 'shop' creation)!";
	}

	qe.prepare("CREATE TABLE IF NOT EXISTS listSettings(pDate TEXT, pList TEXT, headerState TEXT)");
	if(!qe.exec()){
		qCritical()<<"shoppingListsBrowser::onExportList(): Can't create database(error during table 'listSettings' creation)!";
	}

	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	// get export
	q.prepare("SELECT DISTINCT s.oid,sTitle,sAddress,sGeoLoc FROM purchase p,shop s WHERE pDate=:DT AND pList=:LST AND p.pShop=s.oid");
	q.bindValue(":DT", wDate);
	q.bindValue(":LST", wList);
	q.exec();

	qe.prepare("INSERT INTO shop(oid,sTitle,sAddress,sGeoLoc) VALUES(:I,:T,:A,:G)");
	while(q.next()){
		qe.bindValue(":I", q.value(0));
		qe.bindValue(":T", q.value(1));
		qe.bindValue(":A", q.value(2));
		qe.bindValue(":G", q.value(3));
		qe.exec();
	}

	// export purchases
	q.prepare("SELECT pDate,pList,pShop,pSec,pCat,pTitle,pPrcP,pCntP,pPrcB,pCntB,pJoy,pDone FROM purchase WHERE pDate=:DT AND pList=:LST");
	q.bindValue(":DT", wDate);
	q.bindValue(":LST", wList);
	q.exec();

	qe.prepare("INSERT INTO purchase(pDate,pList,pShop,pSec,pCat,pTitle,pPrcP,pCntP,pPrcB,pCntB,pJoy,pDone) "
			   "VALUES(:D,:L,:SH,:SC,:C,:T,:PP,:CP,:PB,:CB,:J,:DN)");
	while(q.next()){
		qe.bindValue(":D", q.value(0));
		qe.bindValue(":L", q.value(1));
		qe.bindValue(":SH", q.value(2));
		qe.bindValue(":SC", q.value(3));
		qe.bindValue(":C", q.value(4));
		qe.bindValue(":T", q.value(5));
		qe.bindValue(":PP", q.value(6));
		qe.bindValue(":CP", q.value(7));
		qe.bindValue(":PB", q.value(8));
		qe.bindValue(":CB", q.value(9));
		qe.bindValue(":J", q.value(10));
		qe.bindValue(":DN", q.value(11));
		qe.exec();
	}

	// export list`s visual settings
	q.prepare("SELECT headerState FROM listSettings WHERE pDate=:DT AND pList=:LST");
	q.bindValue(":DT", wDate);
	q.bindValue(":LST", wList);
	q.exec();

	if(q.next()){
		qe.prepare("INSERT INTO listSettings(pDate, pList, headerState) "
				   " VALUES(:DT,:L,:H)");
		qe.bindValue(":DT", wDate);
		qe.bindValue(":L", wList);
		qe.bindValue(":H", q.value(0));
		qe.exec();
	}
}

void shoppingListsBrowser::onPrintList()
{
	QPrinter *printer= new QPrinter(QPrinter::PrinterResolution);
	bool onlyUnchecked= false;
	QPrintDialog printDialog(printer, this);
	if(printDialog.exec() != QDialog::Accepted) return;

	QString wDate= listP->index(listV->currentIndex().row(),0).data().toDate().toString("yyyy-MM-dd");
	QString wList= listP->index(listV->currentIndex().row(),1).data().toString();
	QString txt="<p style=\"font-size:14pt;\">";
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	// get sections
	q.prepare("SELECT DISTINCT pSec FROM purchase WHERE pDate=:DT AND pList=:LST ORDER BY pSec ASC");
	q.bindValue(":DT", wDate);
	q.bindValue(":LST", wList);
	q.exec();
	QStringList secs;
	while(q.next()) if(!q.value(0).toString().isEmpty()) secs.append(q.value(0).toString());

	foreach(QString sec, secs){
		if(onlyUnchecked){
			q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND pSec=:SEC AND pDone<>1 GROUP BY pTitle ORDER BY pTitle ASC");
			q.bindValue(":DT", wDate);
			q.bindValue(":LST", wList);
			q.bindValue(":SEC", sec);
		}else{
			q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND pSec=:SEC GROUP BY pTitle ORDER BY pTitle ASC");
			q.bindValue(":DT", wDate);
			q.bindValue(":LST", wList);
			q.bindValue(":SEC", sec);
		}
		q.exec();
		txt+= "<br><b><u>"+sec+"</u></b><br>";
		while(q.next()){
			txt+= q.value(0).toString()+" - "+q.value(1).toString()+"<br>\n";
		}
	}
	// print purchases with 'sec' set to NULL
	if(onlyUnchecked){
		q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND (pSec ='' OR pSec IS NULL) AND pDone<>1 GROUP BY pTitle ORDER BY pTitle ASC");
		q.bindValue(":DT", wDate);
		q.bindValue(":LST", wList);
	}else{
		q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND (pSec ='' OR pSec IS NULL) GROUP BY pTitle ORDER BY pTitle ASC");
		q.bindValue(":DT", wDate);
		q.bindValue(":LST", wList);
	}
	if(q.exec()){
		txt+= "<br><b><u>---</u></b><br>";
		while(q.next()){
			txt+= q.value(0).toString()+" - "+q.value(1).toString()+"<br>\n";
		}
	}

	txt+="</p>";

	QTextDocument *td= new QTextDocument(this);
	td->setHtml(txt);
	td->print(printer);
	delete td;
}

void shoppingListsBrowser::updateTotals()
{
	double tCostP=0,tCost=0;
	for(int i=0; i<listP->rowCount(); i++){
		tCostP+= listP->mapFromSource(listD->index(listP->mapToSource(listP->index(i,0)).row(),3)).data().toDouble();
		tCost+= listP->mapFromSource(listD->index(listP->mapToSource(listP->index(i,0)).row(),4)).data().toDouble();
	}
	sumPlanV->setText(QString::number(tCostP,'f',2));
	sumBuyV->setText(QString::number(tCost,'f',2));

}

void shoppingListsBrowser::updateData()
{
	listD->restore(listDateBegin->date(), listDateEnd->date());
	updateTotals();
}

void shoppingListsBrowser::restore()
{
	listFilterV->clear();
	listFilterV->addItem(tr("*"));

	QSettings cfg(qApp->property("dataDir").toString()+"/settings.ini", QSettings::IniFormat);
	QString flt= cfg.value("/listBrowser/filter").toString();
	if(flt!="*"){
		listFilterV->addItem(flt);
		if(!flt.isEmpty())listFilterV->setCurrentIndex(listFilterV->findText(flt));
	}
	QDate db= cfg.value("/listBrowser/dateBegin",QDate::currentDate()).toDate();
	QDate de= cfg.value("/listBrowser/dateEnd",QDate::currentDate()).toDate();
	if(db<=de){
		listDateBegin->setDate(db);
		listDateEnd->setDate(de);
	}

	updateData();
}

void shoppingListsBrowser::onHeaderSectionShow(bool isShow)
{
	QAction *act= qobject_cast<QAction*>(sender());
	if(act == 0) return;
	listV->horizontalHeader()->setSectionHidden(act->data().toInt(),!isShow);
}

void shoppingListsBrowser::onSelectionChanged(const QItemSelection &selected, const QItemSelection &deselected)
{
	if(listV->selectionModel()->hasSelection()) emit guiControlsSwitch(true);
	else emit guiControlsSwitch(false);
}
